package com.java.dao.impl;

import com.java.entity.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * TODO
 *
 * @author 村头老杨头
 * @version 1.0
 * @date 2021/9/26 17:36
 */
public class StudentDaoImpl {
    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://127.0.0.1:3306/edu";
    private String username = "root";
    private String password = "123456";

    //根据学生编号查询学生
    public Student getStudentById(String sno) throws SQLException, ClassNotFoundException {
        Student student = new Student();
        Connection conn = getConnection();
        String sql = "select * from student where sno=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,sno);
        ResultSet rs = ps.executeQuery();
        if(rs.next()){
            student.setSno(rs.getString("Sno"));
            student.setSname(rs.getString("Sname"));
            student.setSsex(rs.getString("Ssex"));
            student.setSbirthday(rs.getDate("Sbirthday"));
            student.setClazz(rs.getString("Clazz"));
        }
        rs.close();
        ps.close();
        conn.close();
        return student;
    }
    //查所有学生信息
    public List<Student> getStudentList() throws SQLException, ClassNotFoundException {
        List<Student> studentList = new ArrayList<>();
        Connection conn = getConnection();
        String sql = "select * from student";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Student student = new Student();
            student.setSno(rs.getString("Sno"));
            student.setSname(rs.getString("Sname"));
            student.setSsex(rs.getString("Ssex"));
            student.setSbirthday(rs.getDate("Sbirthday"));
            student.setClazz(rs.getString("Clazz"));
            studentList.add(student);
        }
        rs.close();
        ps.close();
        conn.close();
        return studentList;
    }
    //添加学生信息
    public int addStudent(Student student) throws SQLException, ClassNotFoundException {
        Connection conn = getConnection();
        String sql = "insert into student  values (?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,student.getSno());
        ps.setObject(2,student.getSname());
        ps.setObject(3,student.getSsex());
        ps.setObject(4,student.getSbirthday());
        ps.setObject(5,student.getClazz());
        //影响行数
        int i = ps.executeUpdate();
        ps.close();
        conn.close();
        return i;
    }
    //删除学生信息
    public int deleteStudent(String sno) throws SQLException, ClassNotFoundException {
        Connection conn = getConnection();
        String sql = "delete from student where sno=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,sno);
        //影响行数
        int i = ps.executeUpdate();
        ps.close();
        conn.close();
        return i;
    }
    //修改学生信息
    public int updateStudent(Student student) throws SQLException, ClassNotFoundException {
        Connection conn = getConnection();
        String sql = "update student set sname=?,ssex=?,sbirthday=?,clazz=? where sno=?";
        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,student.getSname());
        ps.setObject(2,student.getSsex());
        ps.setObject(3,student.getSbirthday());
        ps.setObject(4,student.getClazz());
        ps.setObject(5,student.getSno());
        //影响行数
        int i = ps.executeUpdate();
        ps.close();
        conn.close();
        return i;
    }
    //加载驱动并创建连接
    private Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        return DriverManager.getConnection(url,username,password);
    }
}
